package com.synchrona.syncDataAccess;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.synchrona.account.Account;
import com.synchrona.common.Common;
import com.synchrona.update.Update;
import com.synchrona.updateSyncManager.UpdateProcessor;
import com.synchrona.user.User;



public class SyncDataAccess {

	private PreparedStatement prpst = null;
	private Connection connection = null;
	private CallableStatement cs;
	
	public SyncDataAccess(){
		
	}
	
	//Gets the user for a user name
	public User getUser(String userName) throws SQLException{
		User user = new User();
		String name = userName;
		String parameter = "UserName";
		try {
			connection = getConnection();
			cs = connection.prepareCall("{call spGetUserDetail(?)}");
			cs.setString(parameter, name);
			
			ResultSet result = (ResultSet)cs.executeQuery();
			if(result != null){
				user = getProcessedUser(result);
			}
		} catch (Exception e) {
			e.printStackTrace();
			
		}finally{
			cs.close();
			connection.close();
		}
		
		return user;
		
	}
	
	//Returns the accout list for user id
	public List<Account> getUserAccounts(int userId) throws SQLException{
		List<Account> userAccounts = new ArrayList<Account>();
		String parameter = Common.colAccountUserId;
		try {
			connection = getConnection();
			cs = connection.prepareCall("{call spGetUserAccounts(?)}");
			cs.setInt(parameter, userId);
			ResultSet resultSet = cs.executeQuery();
			if(resultSet != null){
				userAccounts = getProcessedAccounts(resultSet);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			cs.close();
			connection.close();
		}
		
		return userAccounts;
	}
	
	//Returns the user for account ID
	public String getUserForAccountId(String userAccountId) throws SQLException{
		String parameter = Common.colAccountUserAccountId;
		String userName = null;
		try {
			connection = getConnection();
			cs = connection.prepareCall("{call spGetUserForAccountId(?)}");
			cs.setString(parameter, userAccountId);
			ResultSet resultSet = cs.executeQuery();
			if(resultSet != null){
				while(resultSet.next()){
				userName = resultSet.getString(Common.colMasterUserName);
				}
			}
			resultSet.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			cs.close();
			connection.close();
		}
		return userName;
	}
	
	///Returns the connection
	public Connection getConnection() throws Exception{
		try{
			java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			System.out.println("Connecting to the database server.....");
			connection = DriverManager.getConnection("jdbc:sqlserver://localhost\\SQLEXPRESS;user=sa;password=060056E;database=Synchrona");
			System.out.println("Connected..");
			
		}catch (Exception e) {
			e.printStackTrace();
		} 
		return connection;
	}

	//Returns the user object
	public User getProcessedUser(ResultSet rs) {
		User user = new User();
		
		try{
			while (rs.next()) {
				int id = rs.getInt(Common.colMasterId);
				String stat = rs.getString(Common.colMasterStatus);
				String email = rs.getString(Common.colUserEmail);
				user.setUserId(rs.getInt(Common.colMasterId));;
				user.setStatus(rs.getString(Common.colMasterStatus));
				user.setEmail(rs.getString(Common.colUserEmail));
				user.setUserName(rs.getString(Common.colMasterUserName));
				user.setRegisteredDate(rs.getDate(Common.colUserRegDate));
				
			}
			rs.close();
		}catch (Exception e) {
			e.printStackTrace();
		}
		return user;
		
	}
	
	
	//Returns the processed account list
	public List<Account> getProcessedAccounts(ResultSet resultSet){
		List<Account> accountList = new ArrayList<Account>();
		try {
			while (resultSet.next()) {
				Account account = new Account();
				account.setAccountId(resultSet.getString(Common.colAccountId));
				account.setAccountEmail(resultSet.getString(Common.colAccountEmail));
				account.setServiceUserName(resultSet.getString(Common.colAccountUserName));
				account.setServicePassword(resultSet.getString(Common.colAccountPassword));
				account.setServiceId(resultSet.getString(Common.colAccountServiceId));
				accountList.add(account);
				
			}
			resultSet.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return accountList;
	}	
	
	public void updateDatabase(Update update){
		try {
		    
			User user = (User)update.getUser();
			
			connection = getConnection();
			cs = connection.prepareCall("{call spInsertUpdateData(?,?,?,?,?,?)}");
			cs.setString(Common.colUpdateType, update.getUpdateType());
			System.out.println(update.getUpdateType());
			cs.setString(Common.colUpdateData, update.getData());
			System.out.println(update.getData());
			cs.setObject(Common.colUpdateTime, update.getUpdateTime());
			System.out.println(update.getUpdateTime());
			cs.setInt(Common.colUpdateUserId, user.getUserId());
			cs.setInt(Common.colUpdateServiceId, update.getService().index());
			cs.setInt(Common.colUpdateStatus, update.getUpdateStatus().index());
			cs.executeUpdate();
			System.out.println(update.getUpdateTime());
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
